Learning Objectives

After completing this lesson, you’ll be able to:

• Expose adjacent feature attributes.
• Use adjacent feature attributes.
• Handle missing values in attribute manipulation.

Resources

• Complete workspace
• Precipitation.xlsx

Introduction

You're working on a project mapping monthly precipitation (rainfall) in the city. You have been given a dataset like so: 

Unfortunately, the numbers are a cumulative amount, and you wanted to map individual figures for each month. Rather than reaching into your desk drawer for a calculator, you decide to use FME to do the calculations!

1) Create Workspace

Open FME Workbench (2021.1 or later). Create a new workspace to translate the data as follows:

When creating the workspace, check the Parameters for the reader to ensure FME recognizes the headers at the top of each column.

2) Add AttributeManager

To calculate precipitation for any given month, you need to subtract the previous month's cumulative total from the current month's cumulative total.

With FME you can use the adjacent feature attribute functionality to fetch the previous month's number.

So, place an AttributeManager transformer between the reader and writer feature types:

3) Set AttributeManager Parameters #1

Inspect the AttributeManager's parameters.

Click the arrow next to Advanced: Attribute Value Handling to expand the options. Check the box marked Enable Adjacent Feature Attributes. In the fields provided enter "1" for the Number of Prior Features to keep.

Next set the parameter Substitute Missing, Null and Empty by to "Default Value" and enter "0" into the Default Value field:

4) Set AttributeManager Parameters #2

Now let's calculate the new precipitation value.

In the Attribute Value field for the Precipitation attribute, click the drop-down arrow, and click Open Arithmetic Editor:

In the Arithmetic Editor use the menu on the left to select:

• The FME Feature Attribute Precipitation
• The Math Operator – (minus)
• The FME Feature Attribute Precipitation for feature[-1]

All of which should leave you with an expression looking like this:

@Value(Precipitation)-@Value(feature[-1].Precipitation)

Now you can see why it was so important to set the substitution field because it’s uncertain what result would occur from the above when feature[-1].Precipitation is missing!

Click OK to close the Arithmetic Editor, and then accept the parameter changes.

5) Save and Run Workspace

Save the workspace and then run it. Inspect the output.

The numbers start out looking correct but quickly become wrong. Not even in Vancouver does it rain 623 mm in a single month!

The problem is this: unlike other occasions in FME, here we can’t simply overwrite the attribute we are working with. That’s because it skews the next calculation. i.e. the calculation for March needs to operate on February's original number, but instead, it receives the value we've just overwritten it with!

The only way to solve this is by creating a new attribute.

6) Adjust Workspace

Edit the writer schema by renaming the destination attribute Precipitation to MonthlyPrecipitation:

Now return to the AttributeManager and change it to create an entirely new attribute called MonthlyPrecipitation. You can copy and paste the Attribute Value formula to save time; just change the Output Attribute name and ensure Action is set to "Set Value":

It's a pain to have to do, but blame me for leading you in the wrong direction at first! You can't even just rename Precipitation to MonthlyPrecipitation since, whatever you call it, it still fetches an incorrect value. You'll have to reset its Action field to "Do Nothing" and then create a new attribute.

7) Re-Run Workspace.

Save the workspace.

Before you re-run the workspace, check the writer parameter called Overwrite Existing File in the Navigator.

Set it to Yes – if it isn’t already – so the output overwrites the destination dataset and doesn’t just append this data to the same spreadsheet.

Also, make sure the file you are writing to is not already open in Excel (or any other editor).

Re-run the workspace.

Inspect the output. This time the numbers should be correct: